﻿using System.Collections.Generic;
using System.Text;
using ContentMaintenanceDashboard.Classes;
using umbraco.DataLayer;
using umbraco.BusinessLogic;
using System.Data;
using System.Data.SqlClient;

namespace ContentMaintenanceDashboard.Helpers
{
    /// <summary>
    /// Class responsible for all Database actions.
    /// </summary>
    public class DataHelper
    {
        /// <summary>
        /// Returns all the documents from The Umbraco Databse
        /// </summary>
        internal static DataTable GetAllDocuments(SearchFilter searchFilter)
        {
            List<SqlParameter> parameterlist = new List<SqlParameter>();
            DataSet ds = new DataSet("result");
            StringBuilder sb = new StringBuilder();

            //Build the base query
            sb.Append("SELECT DISTINCT  umbracoNode.Id,umbracoNode.Text, isNull((select top 1 published from cmsDocument where published = 1 And nodeId = umbracoNode.Id ),0)  AS Published, DocType.Text as nodeTypeName  ");
            sb.Append("from umbracoNode ");
            sb.Append("INNER Join cmsDocument ON umbracoNode.Id = cmsDocument.NodeId   "); 
            sb.Append("INNER JOIN cmsContent ON umbracoNode.Id = cmsContent.NodeId ");
            sb.Append("INNer JOIN umbracoNode DocType ON cmsContent.contentType = DocType.Id and DocType.nodeObjectType = 'a2cb7800-f571-4787-9638-bc48539a0efb'  "); //Select document type
            sb.Append("INNER JOIN cmsContentType ON cmsContent.contentType = cmsContentType.NodeId ");

            if (searchFilter.SearchInContent)
            {
                sb.Append("left JOIN cmsPropertyData ON umbracoNode.Id = cmsPropertyData.contentNodeId ");
            }
            sb.Append("WHERE (umbracoNode.nodeObjectType = 'c66ba18e-eaf3-4cff-8a22-41b16d66a972')"); //Only select content elements
            sb.Append("AND not umbracoNode.path like '-1,-20%' "); //Not in recycle bin

            //Build criteria
            //Search for a node name if specified
            if (!string.IsNullOrEmpty(searchFilter.Name))
            {
                sb.Append(" And ( umbracoNode.Text like '%' + @NodeName + '%' ");

                //Also search in content
                if (searchFilter.SearchInContent)
                {
                    sb.Append("  Or cmsPropertyData.dataNvarchar like '%' + @NodeName + '%' Or cmsPropertyData.dataNText like '%' + @NodeName + '%'  ");
                }
                sb.Append(" ) ");

                parameterlist.Add(new SqlParameter("NodeName", searchFilter.Name));
            }

            //Search in a specific parent based on the path property of UmbracoNode
            if(!string.IsNullOrEmpty(searchFilter.Parent))
            {
                sb.Append(" And (  umbracoNode.parentId = @ParentNode ) ");
                parameterlist.Add(new SqlParameter("ParentNode", searchFilter.Parent));
            }

            //Search for a document type if specified
            if (searchFilter.DocumentType != string.Empty)
            {
                sb.Append("And cmsContentType.alias = @Alias ");
                parameterlist.Add(new SqlParameter("Alias", searchFilter.DocumentType));
            }

            //Search for a templateif specified
            if (searchFilter.Template != 0)
            {
                sb.Append("And cmsDocument.templateid = @template ");
                parameterlist.Add(new SqlParameter("template", searchFilter.Template));
            }

            //Search for a published state if specified
            if (searchFilter.Published.HasValue)
            {
                sb.Append("And isNull((select top 1 published from cmsDocument where published = 1 And nodeId = umbracoNode.Id ),0) = @Published");
                parameterlist.Add(new SqlParameter("Published", searchFilter.Published.Value));
            }


            //

            using (SqlConnection sqlConnection = new SqlConnection(Application.SqlHelper.ConnectionString))
            {
                sqlConnection.Open();
                SqlCommand sqlCommand = new SqlCommand(sb.ToString(), sqlConnection);

                sqlCommand.Parameters.AddRange(parameterlist.ToArray());

                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
                sqlDataAdapter.Fill(ds, "searchresult");
            }

            //Return data as datatable
            return ds.Tables[0];
        }

    }


}
